package com.bdwm.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bdwm.db.DataBaseConn;
import com.bdwm.model.BorrowDtl;
import com.bdwm.model.Constants;
import com.bdwm.model.Device;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.util.JSONStringer;


public class BorrowInfoDBService {
	
	//获取所有已借出设备的信息
	public static List<BorrowDtl> getAllBorrowInfo() {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final String sql = "select device_name, borrow_time,user_name,os_type,version_type,device_resolution,device_dept,remark from borrow_info t1 join user_info t2 on t1.user_id=t2.user_id join device_info t3 on t1.device_id=t3.device_id where t1.borrow_status=1";
		List<BorrowDtl> list = new ArrayList<BorrowDtl>();
		try {
			conn = DataBaseConn.mysqlConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				BorrowDtl borrow_del = new BorrowDtl();
				borrow_del.setDeviceName(rs.getString("device_name"));
				borrow_del.setBorrowTime(rs.getDate("borrow_time"));
				borrow_del.setUserName(rs.getString("user_name"));
				borrow_del.setOsType(rs.getString("os_type"));
				borrow_del.setVersionType(rs.getString("version_type"));
				borrow_del.setDeviceResolution(rs.getString("device_resolution"));
				borrow_del.setDeviceDept(rs.getString("device_dept"));
				borrow_del.setRemark(rs.getString("remark"));
				list.add(borrow_del);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return list;
		
	}
	
	//获取所有未借出设备的信息
	public static List<Device> getFreeDevices(String device_belongs) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
	//	String sql = "select device_name,device_assetnum,os_type,version_type,device_resolution,device_belongs,device_dept,remark from device_info where device_belongs="+'"device_belongs'";
		String sql = "select device_name,device_assetnum,os_type,version_type,device_resolution,device_belongs,device_dept,remark from device_info where device_belongs='"+device_belongs+"'";
		List<Device> deviceList = new ArrayList<Device>();
		try {
			conn = DataBaseConn.mysqlConn();
			ps = conn.prepareStatement(sql);
		//	ps.setString(1, device_belongs);
			rs = ps.executeQuery();
			while(rs.next()) {
				Device device = new Device(); 
//				device.setDeviceID(rs.getInt("device_id"));
				device.setDeviceName(rs.getString("device_name"));
				device.setDeviceAssetNum(rs.getString("device_assetnum"));
				device.setOsType(rs.getString("os_type"));
				device.setVersionType(rs.getString("version_type"));
				device.setDeviceResolution(rs.getString("device_resolution"));
				device.setDeviceBelongs(rs.getString("device_belongs"));
				device.setDeviceDept(rs.getString("device_dept"));
				device.setRemark(rs.getString("remark"));
				deviceList.add(device);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DataBaseConn.closeDB(rs, ps, conn);
		}
		
		return deviceList;
		
	}
	
	//通过设备名称判断设备的借出状态，borrow_status判断
	public static int checkIfDeviceBorrow(String device_name, String user_name){
		int isBorrow = -1; //1表示已借出，0表示可借，其他值表示设备从未被借用过 借用需要insert；
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DataBaseConn.mysqlConn();
			String sql = "select borrow_status from borrow_info as t1 join device_info as t2 on t1.device_id = t2.device_id join user_info as t3 on t1.user_id = t3.user_id where t2.device_name = '"+ device_name +"'" + " and t3.user_name = '" + user_name + "'";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			int status = -1;     //借用状态，status=1 表示已借；status=0 表示已还；
			while(rs.next()){
				status = rs.getInt("borrow_status");
			}
			if(1 == status){
				isBorrow = 1;
			}else if(0 == status){
				isBorrow = 0;
			}
			
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DataBaseConn.closeDB(rs, ps, conn);
		}
		return isBorrow ;
		
	}
	
	/**
	 *
	 * @throws ClassNotFoundException 
	 * @author：changna
	 * @Description：借用设备接口
	 * @time：Mar 12, 2018，2:58:37 PM
	 *
	 */
	public static int borrowDevice(BorrowDtl borrowdtl, int isborrowed) throws ClassNotFoundException{
		int status = Constants.ERROR;   //判断接口是否执行成功
	
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int deviceId = 0;
		int userId = 0;
		try {
			conn = DataBaseConn.mysqlConn();
			String deviceIdsql = "select device_id from device_info where device_name = '" + borrowdtl.getDeviceName() + "'";
			String userIdsql = "select user_id from user_info where user_name = '" + borrowdtl.getUserName() + "'";
			String insertSql = "INSERT INTO borrow_info(device_id, user_id, borrow_time, borrow_status) VALUES (?,?,?,?)";
			String updateSql = "update borrow_info set borrow_status = 1,borrow_time = ? where device_id = ? and user_id = ?";
			String updatebelongsSql = "update device_info set device_belongs = ? where device_name = ?";
			conn.setAutoCommit(false);//将此连接的自动提交模式设置为给定状态。false为禁用
			
			ps = conn.prepareStatement(deviceIdsql);
			rs = ps.executeQuery(deviceIdsql);
			while(rs.next()){
				deviceId = rs.getInt("device_id");
			}
			ps.close();
			rs.close();
			
			ps = conn.prepareStatement(userIdsql);
			rs = ps.executeQuery(userIdsql);
			while(rs.next()){
				userId = rs.getInt("user_id");
			}
			ps.close();
			rs.close();
			
			if(0 == isborrowed){            //表示设备曾经被借出，只需要update借用表状态即可
				ps = conn.prepareStatement(updateSql);
				ps.setDate(1, borrowdtl.getBorrowTime());
				ps.setInt(2, deviceId);
				ps.setInt(3, userId);
				status = ps.executeUpdate();
			    
				ps.close();
				
			}else{         //设备从未被借出，需要insert借用关系到借用表中
				ps = conn.prepareStatement(insertSql);
				ps.setInt(1, deviceId);
				ps.setInt(2, userId);
				ps.setDate(3, (Date) borrowdtl.getBorrowTime());
				ps.setInt(4, 1);
				ps.executeUpdate();
				
				ps.close();
			}
			
			ps = conn.prepareStatement(updatebelongsSql);     //借用操作完成后，需要更新device的belongs值
			ps.setString(1, borrowdtl.getUserName());
			ps.setString(2, borrowdtl.getDeviceName());
			status = ps.executeUpdate();
			
			if(status != 0){ 
				status = Constants.SUCCESS; 
				conn.commit();
			}else{
				status = Constants.ERROR; 
				conn.rollback();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DataBaseConn.closeDB(null, ps, conn);
		}
		
		return status;
	}
	
	/**
	 *
	 * 
	 * @author：changna
	 * @Description：归还设备接口
	 * @time：Mar 13, 2018，3:18:37 PM
	 *
	 */
	@SuppressWarnings("null")
	public static int returnDevice(BorrowDtl borrowdtl){
		int status = Constants.ERROR;
		
		Connection conn = null;
		PreparedStatement ps =null;
		ResultSet rs = null;
		int deviceId = 0;
		int userId = 0; 
		try {
			String deviceIdsql = "select device_id from device_info where device_name = '" + borrowdtl.getDeviceName() + "'";
			String userIdsql = "select user_id from user_info where user_name = '" + borrowdtl.getUserName() + "'";
			String reborrowsql = "update borrow_info set borrow_status = 0,borrow_time = ? where device_id = ? and user_id = ?";
			String redevicesql = "update device_info set device_belongs = 'admin' where device_name = ?";
			
			conn = DataBaseConn.mysqlConn();
			conn.setAutoCommit(false);//将此连接的自动提交模式设置为给定状态。false为禁用
			
			//找出device_id
			ps = conn.prepareStatement(deviceIdsql);
			rs = ps.executeQuery(deviceIdsql);
			while(rs.next()){
				deviceId = rs.getInt("device_id");
			}
			ps.close();
			rs.close();
			
			//找出user_id
			ps = conn.prepareStatement(userIdsql);
			rs = ps.executeQuery(userIdsql);
			while(rs.next()){
				userId = rs.getInt("user_id");
			}
			ps.close();
			rs.close();
			
			ps = conn.prepareStatement(reborrowsql);
			ps.setDate(1, borrowdtl.getBorrowTime());;
			ps.setInt(2, deviceId);
			ps.setInt(3, userId);
			ps.executeUpdate();
			ps.close();
			
			ps = conn.prepareStatement(redevicesql);
			ps.setString(1, borrowdtl.getDeviceName());
			status = ps.executeUpdate();

			if(status != 0){ 
				status = Constants.SUCCESS; 
				conn.commit();
			}else{
				status = Constants.ERROR; 
				conn.rollback();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DataBaseConn.closeDB(null, ps, conn);
		}
		return status;
	}
	
	public static void main(String[] args) {
		/*List<BorrowDtl> borrList = BorrowInfoDBService.getAllBorrowInfo();
		//生成Json数据 
		JSONArray jsonArr = new JSONArray();
		JSONObject json = new JSONObject();
		
		json.accumulate("status", 0);
		for(BorrowDtl borrowDtl: borrList) {
			JSONObject borrowDtlJson = new JSONObject();
			borrowDtlJson.accumulate("userName", borrowDtl.getUserName());
			borrowDtlJson.accumulate("deviceName", borrowDtl.getDeviceName());
			borrowDtlJson.accumulate("borrowTime", borrowDtl.getBorrowTime().toString());
			jsonArr.add(borrowDtlJson);
		}
		json.accumulate("data", jsonArr);
		System.out.println(json.toString());*/
//		List<Device> deviceList = BorrowInfoDBService.getFreeDevices("admin");
//		JSONObject json = new JSONObject();
//		json.accumulate("status", 0);
//		JSONArray jsonArr = new JSONArray();
//		for(Device device: deviceList) {
//			JSONObject deviceJson = new JSONObject();
//			deviceJson.accumulate("deviceName",device.getDeviceName());
//			deviceJson.accumulate("deviceAssetNum",device.getDeviceAssetNum());
//			deviceJson.accumulate("osType",device.getOsType());
//			deviceJson.accumulate("versionType",device.getVersionType());
//			deviceJson.accumulate("deviceResolution",device.getDeviceResolution());
//			deviceJson.accumulate("deviceBelongs",device.getDeviceBelongs());
//			deviceJson.accumulate("deviceDept",device.getDeviceDept());
//			deviceJson.accumulate("remark",device.getRemark());
//			jsonArr.add(deviceJson);
//		}
//		json.accumulate("data", jsonArr);
//		System.out.println(json.toString());
		

		
	}
	
	
	

}
